
use ${nc_clean_data}/mb_full, clear

destring daysmem, force gen(days)
destring grade, gen(g) force

replace days = daysN if days==.
order mastid schoolyr g lea schlcode n *_score *_sds *_tdt days

replace g = 12 if g==13

gsort mastid schoolyr g - m_sds - r_sds -a_sds -e_sds -days
bysort mastid schoolyr : gen n=_n
keep if n ==1
drop n

egen min_schoolyr = min(schoolyr), by(mastid)
egen min_g = min(g), by(mastid)
egen max_schoolyr = max(schoolyr), by(mastid)
egen max_g = max(g), by(mastid)

forvalues g = 3/12 {
	gen ggg = (g==`g')
	egen hasg`g' = max(ggg), by(mastid)
	drop ggg
}

gen i_sm = .
replace i_sm = 0 if sex!=""
replace i_sm = 1 if sex=="M"

gen i_sf = .
replace i_sf = 0 if sex!=""
replace i_sf = 1 if sex=="F"

gen i_bl = .
replace i_bl = 0 if ethnic!=""
replace i_bl = 1 if ethnic=="BLCK" | ethnic=="B" | ethnic=="4"

gen i_wh = .
replace i_wh = 0 if ethnic!=""
replace i_wh = 1 if ethnic=="WHTE" | ethnic=="W" | ethnic=="5"

gen i_hi = .
replace i_hi = 0 if ethnic!=""
replace i_hi = 1 if ethnic=="HISP" | ethnic=="H" | ethnic=="3"

gen i_oth = .
replace i_oth = 0 if ethnic!=""
replace i_oth = 1 if i_hi==0 & i_wh==0 & i_bl==0

gen race = ""
replace race = "bl" if i_bl==1
replace race = "wh" if i_wh==1
replace race = "hi" if i_hi==1
replace race = "oth" if i_oth==1

gen i_lep = .
replace i_lep = inrange(schoolyr, real(lep_first_year), real(lep_year_exited)) if lep_first!=""
replace i_lep = lep_current=="Y" if lep_current!=""
replace i_lep = lep=="Y" if lep!=""

gen i_eds = .
replace i_eds = 0 if eds == "N"
replace i_eds = 1 if eds == "Y"

gen i_frl = .
replace i_frl = 0 if (frl!="" & frl!="NULL")| eds=="N"
replace i_frl = 1 if frl=="F" | frl=="R" | frl=="T" ///
					| frl=="1" | frl=="2" | frl=="3" | frl=="5" | frl=="6" | eds=="Y"

gen i_fl = .
replace i_fl = 0 if frl!="" & frl!="NULL"
replace i_fl = 1 if frl=="F" | frl=="2" | frl=="3" | frl=="5" | frl=="6"

gen i_rtl = .
replace i_rtl = 0 if frl!="" & frl!="NULL"
replace i_rtl = 1 if frl=="R" | frl=="T" ///
					| frl=="1" | frl=="3"

gen i_mig = (migrant=="Y")
replace i_mig = . if migrant==""


foreach x in bl wh hi eds frl fl rtl lep sm mig {
	egen ever_`x' = max(i_`x'), by(mastid)
}

order mastid race i_sf i_frl i_rtl schoolyr g lea schlcode n *_sds *_tdt days

foreach s in r m a e {
	format `s'_exem %4s
}

capture noisily drop birthdt
merge m:1 ${nc_raw_data}/exactdob, keepusing(birthdt) gen(_m_db) keep(match master)

rename birthdt db

egen max_db = max(db), by(mastid)
replace db = max_db if db==.

format db max_db %tdnn/dd/CCYY

gen z = .
gen cutyr=.

forvalues yr=1970/2017 {
	replace z = db-mdy(1,1,`yr') if inrange(db,mdy(7,1,`yr'-1),mdy(6,30,`yr'))   //Alex: fixed this (July 1 to June 30 is -6 months to +6 months)... won't really factor in though.
	replace cutyr = `yr' if inrange(db,mdy(7,1,`yr'-1),mdy(6,30,`yr'))
}
gen taxyr_cut = cutyr-1

gen belowcut = (z<0)
replace belowcut = . if z==.
gen agecyr = schoolyr-cutyr

gen dbm = month(db)

rename min_schoolyr min_obsyr
rename max_schoolyr max_obsyr

drop daysNIV daysmem reporting_year bdate

merge m:1 mastid schoolyr using ${nc_clean_data}/accdemo, keepusing(i_aig* *abs times* days*) gen(_m_demo) keep(match master)
merge m:1 mastid using ${nc_clean_data}/exit_pub_1ob, keepusing(*grad* *drop* *death min_schoolyr max_schoolyr) gen(_m_exit1ob) keep(match master)

rename min_schoolyr exit_minyr
rename max_schoolyr exit_maxyr

gen i_anysusp = (times_in_susp>0 | times_out_susp>0)
replace i_anysusp = . if times_in_susp==.

foreach x in grad exitgrad drop exitdrop exitdeath {
rename i_`x' ever_`x'
}

order lea schlcode mastid db z race i_sf i_frl schoolyr cutyr g agecyr r_sds m_sds a_sds e_sds ///
		min_* max_* *tdt *exem i_* ever_* taxyr_cut belowcut *abs times* days* grade schlname

keep lea schlcode mastid db dbm z race i_sf i_frl schoolyr cutyr g agecyr r_sds m_sds a_sds e_sds r_score m_score a_score e_score ///
		min_* max_* *tdt *exem i_* ever_* taxyr_cut belowcut days grade schlname exit_* drop_yr grad_yr i_aig* *abs times* days*

save  data/ncerc/combined_full_slim, replace

drop if g==.
sort mastid g schoolyr
bysort mastid g: gen n = _n
keep if n==1

keep mastid db dbm z belowcut ever_* schoolyr taxyr_cut cutyr lea schlcode g i_anysusp *_sds *_score min_g max_g min_obsyr max_obsyr


foreach s in m r {
	capture drop xxx
	gen xxx = `s'_score if belowcut==0
	egen `s'_mean_gt0frl = mean(xxx), by(ever_frl schoolyr)
	egen `s'_sd_gt0frl = sd(xxx), by(ever_frl schoolyr)
	gen `s'_sds0frl = (`s'_score-`s'_mean_gt0frl)/`s'_sd_gt0frl

	egen `s'_mean_gt0 = mean(xxx), by(schoolyr)
	egen `s'_sd_gt0= sd(xxx), by( schoolyr)
	gen `s'_sds0= (`s'_score-`s'_mean_gt0)/`s'_sd_gt0

	gen `s'_sdszfrl = `s'_sds0frl
	replace `s'_sdszfrl = 0 if `s'_sds0frl==.

	gen `s'_sdsz= `s'_sds0
	replace `s'_sdsz= 0 if `s'_sds0==.

}
drop *_mean_gt* *_sd_gt* *_score* xxx


reshape wide schoolyr i_anysusp *_sds* lea schlcode, i(mastid) j(g)

drop m_*9 m_*10 m_*11 m_*12 r_*9 r_*10 r_*11 r_*12


foreach x in exitgrad {
	capture drop xxx
	capture drop m0 sd0 m0frl sd0frl xxx
	gen xxx = ever_`x' if belowcut==0
	egen m0 = mean(xxx), by(schoolyr7)
	egen sd0 = sd(xxx), by(schoolyr7)
	gen ever_`x'_sds0 = (ever_`x' - m0)/sd0

	egen m0frl = mean(xxx), by(ever_frl schoolyr7)
	egen sd0frl = sd(xxx), by(ever_frl schoolyr7)
	gen ever_`x'_sds0frl = (ever_`x' - m0frl)/sd0frl

	gen ever_`x'_sdszfrl = ever_`x'_sds0frl
	replace ever_`x'_sdszfrl = 0 if ever_`x'_sds0frl==.

	gen ever_`x'_sdsz= ever_`x'_sds0
	replace ever_`x'_sdsz= 0 if ever_`x'_sds0==.

	drop m0 sd0 m0frl sd0frl xxx
}



forvalues g=6/12 {

	capture drop xxx
	gen xxx = i_anysusp`g' if belowcut==0
	egen m0 = mean(xxx), by(schoolyr`g')
	egen sd0 = sd(xxx), by(schoolyr`g')
	gen i_anysusp_sds0`g' = (xxx - m0)/sd0

	egen m0frl = mean(xxx), by(ever_frl schoolyr`g')
	egen sd0frl = sd(xxx), by(ever_frl schoolyr`g')
	gen i_anysusp_sds0`g'frl = (i_anysusp`g' - m0frl)/sd0frl

	drop m0 sd0 m0frl sd0frl xxx

}

gen ever_mssusp = max(i_anysusp6, i_anysusp7, i_anysusp8)
gen ever_hssusp = max(i_anysusp9, i_anysusp10, i_anysusp11, i_anysusp12)
gen ever_susp = max(ever_mssusp, ever_hssusp)

global g_mssusp = 6
global g_hssusp = 9
global g_susp = 6
foreach x in mssusp hssusp susp {
	capture drop xxx
	gen xxx = ever_`x' if belowcut==0

	egen m0 = mean(xxx), by(schoolyr${g_`x'})
	egen sd0 = sd(xxx), by(schoolyr${g_`x'})
	gen ever_`x'_sds0 = -(ever_`x' - m0)/sd0
	drop m0 sd0

	egen m0frl = mean(xxx), by(schoolyr${g_`x'} ever_frl)
	egen sd0frl = sd(xxx), by(schoolyr${g_`x'} ever_frl)
	gen ever_`x'_sds0frl = -(ever_`x' - m0frl)/sd0frl
	drop m0 sd0 xxx

	gen ever_`x'_sdszfrl = ever_`x'_sds0frl
	replace ever_`x'_sdszfrl = 0 if ever_`x'_sds0frl==.

	gen ever_`x'_sdsz= ever_`x'_sds0
	replace ever_`x'_sdsz= 0 if ever_`x'_sds0==.

}

order mastid db z min_g min_obsyr schoolyr3 schoolyr7 schoolyr9 ever_exitgrad* i_anysusp* ever_*susp


egen m = mean(ever_susp), by(schoolyr8)
egen sd = sd(ever_susp), by(schoolyr8)
gen ever_susp_sds = -(ever_susp - m)/sd
drop m sd

foreach x in exitgrad exitdrop exitdeath grad drop {
	egen m = mean(ever_`x'), by(schoolyr7)
	egen sd = sd(ever_`x'), by(schoolyr7)
	gen ever_`x'_sds = (ever_`x' - m)/sd
	drop m sd
}


foreach v in "" 0 {
egen index_test`v' = rowmean(m_sds`v'3 m_sds`v'4 m_sds`v'5 m_sds`v'6 m_sds`v'7 m_sds`v'8 ///
							r_sds`v'3 r_sds`v'4 r_sds`v'5 r_sds`v'6 r_sds`v'7 r_sds`v'8)


gen indexnm_test`v' = index_test`v'

replace indexnm_test`v' = . if m_sds`v'3==. ///
						| m_sds`v'4==. ///
						| m_sds`v'5==. ///
						| m_sds`v'6==. ///
						| m_sds`v'7==. ///
						| m_sds`v'8==.




foreach y in index_test`v' ever_exitgrad_sds`v' ever_susp_sds`v' {
	summ `y' if belowcut==0 & (inrange(dbm,11,12) | inrange(dbm,1,2))
	local m0_`y' = r(mean)

	summ `y' if belowcut==1 & (inrange(dbm,11,12) | inrange(dbm,1,2))
	local m1_`y' = r(mean)
}


foreach y in index_test`v' ever_exitgrad_sds`v' ever_susp_sds`v' {

	summ `y' if belowcut==0 & ever_frl==0 & (inrange(dbm,11,12) | inrange(dbm,1,2))
	local m0f0_`y' = r(mean)
	summ `y' if belowcut==0 & ever_frl==1 & (inrange(dbm,11,12) | inrange(dbm,1,2))
	local m0f1_`y' = r(mean)

	summ `y' if belowcut==1 & ever_frl==0 & (inrange(dbm,11,12) | inrange(dbm,1,2))
	local m1f0_`y' = r(mean)
	summ `y' if belowcut==1 & ever_frl==1 & (inrange(dbm,11,12) | inrange(dbm,1,2))
	local m1f1_`y' = r(mean)
}


gen indexnm_testgradsusp`v' = (1/2)*indexnm_test`v' + (1/4)*ever_exitgrad_sds`v'  + (1/4)*ever_susp_sds`v'
replace indexnm_testgradsusp`v' = . if indexnm_test`v'==. ///
						| ever_exitgrad_sds`v'==. ///
						| ever_susp_sds`v'==.

gen indexm_testgradsusp`v' = (1/2)*index_test`v' + (1/4)*ever_exitgrad_sds`v'  + (1/4)*ever_susp_sds`v'
replace indexm_testgradsusp`v' = (2/3)*index_test`v' + (1/3)*ever_exitgrad_sds`v'  ///
								if ever_susp_sds`v'==.
replace indexm_testgradsusp`v' = (2/3)*index_test`v' + (1/3)*ever_susp_sds`v' ///
								if ever_exitgrad_sds`v'==.
replace indexm_testgradsusp`v' = (1/2)*ever_exitgrad_sds`v'  + (1/2)*ever_susp_sds`v' ///
								if index_test`v'==.
replace indexm_testgradsusp`v' = index_test`v' ///
								if ever_susp_sds`v'==. & ever_exitgrad_sds`v'==.
replace indexm_testgradsusp`v' = ever_exitgrad_sds`v' ///
								if index_test`v'==. & ever_susp_sds`v'==.
replace indexm_testgradsusp`v' = ever_susp_sds`v' ///
								if index_test`v'==. & ever_exitgrad_sds`v'==.

foreach f in "" "f" {
		gen index`f'_testgrad`v' = (2/3)*index_test`v' + (1/3)*ever_exitgrad_sds`v'
		gen index`f'_testgradsusp`v' = (1/2)*index_test`v' + (1/4)*ever_exitgrad_sds`v'  + (1/4)*ever_susp_sds`v'

		local frl_f = "f0 f1"
		local frl_ = ""
		local r_f0 = "& ever_frl==0"
		local r_f1 = "& ever_frl==1"
		local r_ = ""
		foreach b in 0 1 {

			foreach frl in `frl_`f'' {
			disp `m`b'`frl'_ever_exitgrad_sds`v''
			disp `m`b'`frl'_ever_susp_sds`v''
			disp `m`b'`frl'_index_test`v''
			disp "A"
				replace index`f'_testgrad`v' = (2/3)*index_test`v' + (1/3)*`m`b'`frl'_ever_exitgrad_sds`v'' ///
										if ever_exitgrad_sds`v'==. & belowcut==`b' `r_`frl''

				replace index`f'_testgrad`v' = (2/3)*`m`b'`frl'_ever_exitgrad_sds`v'' + (1/3)*ever_exitgrad_sds`v' ///
										if index_test`v'==. & belowcut==`b' `r_`frl''

				replace index`f'_testgradsusp`v' = (1/2)*index_test`v' + (1/4)*ever_exitgrad_sds`v' + (1/4)*`m`b'`frl'_ever_susp_sds`v''  ///
												if ever_susp_sds`v'==. & belowcut==`b' `r_`frl''

				replace index`f'_testgradsusp`v' = (1/2)*index_test`v' + (1/4)*`m`b'`frl'_ever_exitgrad_sds`v'' + (1/4)*ever_susp_sds`v' ///
												if ever_exitgrad_sds`v'==. & belowcut==`b' `r_`frl''

				replace index`f'_testgradsusp`v' = (1/2)*`m`b'`frl'_index_test`v'' + (1/4)*ever_exitgrad_sds`v'  + (1/4)*ever_susp_sds`v' ///
												if index_test`v'==.	& belowcut==`b' `r_`frl''

				replace index`f'_testgradsusp`v' = (1/2)*index_test`v' + (1/4)*`m`b'`frl'_ever_exitgrad_sds`v'' + (1/4)*`m`b'`frl'_ever_susp_sds`v'' ///
												if ever_susp_sds`v'==. & ever_exitgrad_sds`v'==.	& belowcut==`b' `r_`frl''

				replace index`f'_testgradsusp`v' = (1/2)*`m`b'`frl'_index_test`v'' + (1/4)*ever_exitgrad_sds`v' + (1/4)*`m`b'`frl'_ever_susp_sds`v'' ///
												if index_test`v'==. & ever_susp_sds`v'==. & belowcut==`b' `r_`frl''

				replace index`f'_testgradsusp`v' = (1/2)*`m`b'`frl'_index_test`v'' + (1/4)*`m`b'`frl'_ever_exitgrad_sds`v'' + (1/4)*ever_susp_sds`v' ///
												if index_test`v'==. & ever_exitgrad_sds`v'==.	& belowcut==`b' `r_`frl''
			}
		}
	}
*/
}

gen icomp_index_test=  ( m_sds3!=. ///
						& m_sds4!=. ///
						& m_sds5!=. ///
						& m_sds6!=. ///
						& m_sds7!=. ///
						& m_sds8!=. ///
						& r_sds3!=. ///
						& r_sds4!=. ///
						& r_sds5!=. ///
						& r_sds6!=. ///
						& r_sds7!=. ///
						& r_sds8!=.)


gen icomp_index_testgradsusp =  ( icomp_index_testgrad==1 ///
						&  ever_susp_sds!=. )

gen none = 1

egen a_sds = rowmax(a_sds*)
egen e_sds = rowmax(e_sds*)

gen ae_sds = (a_sds+e_sds)/2
replace ae_sds = a_sds if e_sds==.
replace ae_sds = e_sds if a_sds==.

gen i_a = (a_sds!=.)
gen i_e = (e_sds!=.)

capture gen db_dow = dow(db)

save  ${nc_clean_data}/combined_full_wide, replace
